package com.example.sqlexport.service.impl;

import com.example.sqlexport.entity.DatabaseConfig;
import com.example.sqlexport.mapper.DatabaseConfigMapper;
import com.example.sqlexport.model.request.TestConnectionRequest;
import com.example.sqlexport.service.DatabaseConfigService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

@Service
public class DatabaseConfigServiceImpl implements DatabaseConfigService {

    @Autowired
    private DatabaseConfigMapper databaseConfigMapper;

    @Override
    public List<DatabaseConfig> findAll() {
        return databaseConfigMapper.findAll();
    }

    @Override
    public DatabaseConfig findById(Long id) {
        return databaseConfigMapper.findById(id);
    }

    @Override
    @Transactional
    public void save(DatabaseConfig config) {
        databaseConfigMapper.insert(config);
    }

    @Override
    @Transactional
    public void update(DatabaseConfig config) {
        databaseConfigMapper.update(config);
    }

    @Override
    @Transactional
    public void delete(Long id) {
        databaseConfigMapper.delete(id);
    }

    @Override
    public boolean testConnection(TestConnectionRequest request) {
        String url = String.format("jdbc:mysql://%s:%d/%s?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&connectTimeout=5000&socketTimeout=10000",
                request.getHost(), request.getPort(), request.getDatabaseName());
        
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            try (Connection conn = DriverManager.getConnection(url, request.getUsername(), request.getPassword())) {
                return conn != null && !conn.isClosed();
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("MySQL JDBC Driver not found. Please check your project dependencies.", e);
        } catch (SQLException e) {
            String friendlyMessage = analyzeSQLException(e, request.getHost(), request.getPort(), request.getDatabaseName());
            throw new RuntimeException(friendlyMessage, e);
        } catch (Exception e) {
            throw new RuntimeException("An unexpected error occurred while testing the database connection: " + e.getMessage(), e);
        }
    }

    private String analyzeSQLException(SQLException e, String host, Integer port, String dbName) {
        String message = e.getMessage() != null ? e.getMessage().toLowerCase() : "";
        if (message.contains("access denied")) {
            return "用户名或密码错误，请检查认证信息。";
        } else if (message.contains("unknown database")) {
            return String.format("数据库 '%s' 不存在，请检查数据库名称是否正确。", dbName);
        } else if (message.contains("communications link failure") || message.contains("connect timed out") || message.contains("connection timed out")) {
            return String.format("无法连接到数据库服务器 %s:%d，请检查主机、端口和网络连接。可能原因：服务器未启动、防火墙阻止、网络问题或连接超时。", host, port);
        } else if (message.contains("unknown host")) {
            return String.format("未知主机 '%s'，请检查主机名或IP地址是否正确。", host);
        }
        return "连接数据库时发生SQL错误: " + e.getMessage();
    }
} 